package com.lili.collection.jdbc.utils;

import cn.hutool.core.util.StrUtil;
import com.lili.collection.core.constans.Constans;
import com.lili.collection.core.fields.Field;
import com.lili.collection.jdbc.base.HeaderAttributeConstants;
import com.lili.collection.jdbc.config.HikariPoolConfig;
import com.lili.collection.jdbc.enums.DataType;
import com.lili.collection.jdbc.enums.DatabaseVendor;
import com.lili.collection.jdbc.enums.TransactionIsolationLevel;
import com.lili.collection.jdbc.enums.UnknownTypeAction;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.time.OffsetTime;
import java.util.Date;
import java.util.*;

/**
 * @author lili
 * @description TODO
 * @date 2023/2/15 0:04
 */
@Slf4j
public class JdbcUtil {
    public static final int NANOS_TO_MILLIS_ADJUSTMENT = 1_000_000;
    public static final String FIELD_ATTRIBUTE_NANOSECONDS = "nanoSeconds";

    private static final String[] METADATA_TABLE_TYPE = new String[]{"TABLE"};

    private static final String[] RDBMS_WITHOUT_SCHEMAS = {"mysql", "mariadb", "memsql", "teradata"};

    public static void ensureJdbcDrivers() {
        Set<String> loadedDrivers = new HashSet<>();
        Collections.list(DriverManager.getDrivers()).forEach(driver -> loadedDrivers.add(driver.getClass().getName()));
        for (Driver driver : ServiceLoader.load(Driver.class)) {
            if (loadedDrivers.contains(driver.getClass().getName())) {
                log.debug("驱动'{}'已经从位置：'{}'处加载", driver.getClass().getName(), getClassJarLocation(driver.getClass()));
            } else {
                log.debug("驱动'{}'未注册，正在尝试注册", driver.getClass().getName());
                try {
                    DriverManager.registerDriver(driver);
                    loadedDrivers.add(driver.getClass().getName());
                } catch (SQLException e) {
                    log.error("显示注册驱动：'{}'失败，原因：{}", driver.getClass().getName(), e.getMessage(), e);
                }
            }
        }
    }

    public static HikariDataSource createDataSourceForRead(HikariPoolConfig hikariPoolConfig) {
        HikariDataSource dataSource;
        try {
            dataSource = new HikariDataSource(createDataSourceConfig(hikariPoolConfig, hikariPoolConfig.isAutoCommit(), true));
        } catch (RuntimeException e) {
            log.error(e.getLocalizedMessage(), e);
            throw e;
        }
        return dataSource;
    }

    public static HikariDataSource createDataSourceForWrite(HikariPoolConfig hikariPoolConfig) {
        HikariDataSource dataSource;
        try {
            dataSource = new HikariDataSource(createDataSourceConfig(hikariPoolConfig, hikariPoolConfig.isAutoCommit(), false));
        } catch (RuntimeException e) {
            log.error(e.getLocalizedMessage(), e);
            throw e;
        }
        return dataSource;
    }

    public static HikariDataSource createDataSource(HikariPoolConfig hikariPoolConfig) {
        HikariDataSource dataSource;
        try {
            dataSource = new HikariDataSource(createDataSourceConfig(hikariPoolConfig));
        } catch (RuntimeException e) {
            log.error(e.getLocalizedMessage(), e);
            throw e;
        }
        return dataSource;
    }

    public static void closeQuietly(AutoCloseable c) {
        if (c != null) {
            try {
                c.close();
            } catch (Exception ex) {
                log.debug("关闭时发生异常: {}", ex.toString(), ex);
            }
        }
    }

    public static String getSQLTypeName(Field.Type type) {
        switch (type) {
            case BOOLEAN:
                return "BOOLEAN";
            case CHAR:
                return "CHAR";
            case BYTE:
                return "BINARY";
            case SHORT:
                return "SMALLINT";
            case INTEGER:
                return "INTEGER";
            case LONG:
                return "BIGINT";
            case FLOAT:
                return "FLOAT";
            case DOUBLE:
                return "DOUBLE";
            case DATE:
                return "DATE";
            case TIME:
                return "TIME";
            case DATETIME:
                return "TIMESTAMP";
            case DECIMAL:
                return "DECIMAL";
            case STRING:
                return "VARCHAR";
            case BYTE_ARRAY:
                return "VARBINARY";
            case LIST_MAP:
            case LIST:
                return "ARRAY";
            default:
                throw new RuntimeException(StrUtil.format("不支持类型：", type.name()));
        }
    }

    public static ResultSet getTableMetadata(Connection connection, String schema, String tableName) throws SQLException {
        DatabaseMetaData metadata = connection.getMetaData();
        return metadata.getTables(getCatalog(connection, schema), schema, tableName, METADATA_TABLE_TYPE);
    }

    public static ResultSet getColumnMetadata(Connection connection, String schema, String tableName) throws SQLException {
        DatabaseMetaData metadata = connection.getMetaData();
        return metadata.getColumns(getCatalog(connection, schema), schema, tableName, null);
    }

    public static String getTableName(String schema, String tableName) {
        return getTableName(schema, tableName, false);
    }


    public static String getTableName(String schema, String tableName, boolean caseSensitive) {
        if (StrUtil.isNotEmpty(schema)) {
            if (caseSensitive) {
                return "\"" + schema + "\"." + "\"" + tableName + "\"";
            } else {
                return schema + "." + tableName;
            }
        }

        if (caseSensitive) {
            return "\"" + tableName + "\"";
        }

        return tableName;
    }

    public static StringBuilder generateMultiInsertSqlWithoutValues(String schema, String tableName, Collection<String> fieldNames,String dbTypeName) {
        String tbName = getTableName(schema, tableName);
        StringBuilder builder = new StringBuilder(Constans.EMPTY_STRING);
        builder.append("INSERT INTO ")
                .append(tbName)
                .append(Constans.SPACE_STRING);
        StringBuilder fieldPlaceholder = new StringBuilder(Constans.EMPTY_STRING);
        boolean mysqlDb = isMysqlDb(dbTypeName);
        for (String fieldName : fieldNames) {
            fieldPlaceholder.append(mysqlDb? ("`"+fieldName+"`") : fieldName )
                    .append(",");
        }
        if (fieldPlaceholder.length() > 0)
            fieldPlaceholder.deleteCharAt(fieldPlaceholder.length() - 1);
        if (fieldNames.size() > 0)
            builder.append("(");
        builder.append(fieldPlaceholder);
        if (fieldNames.size() > 0)
            builder.append(")");
        builder.append(" VALUES ");
        return builder;
    }

    private static boolean isMysqlDb(String dbTypeName){
        return "mysql".equalsIgnoreCase(dbTypeName);
    }

    public static String generateSingleInsertSql(String schema, String tableName, Collection<String> fieldNames) {
        StringBuilder builder = generateMultiInsertSqlWithoutValues(schema, tableName, fieldNames,null);
        StringBuilder valuePlaceholder = new StringBuilder(Constans.EMPTY_STRING);
        for (int i = 0; i < fieldNames.size(); i++) {
            valuePlaceholder.append("?,");
        }
        if (valuePlaceholder.length() > 0){
            valuePlaceholder.deleteCharAt(valuePlaceholder.length() - 1);
        }
        valuePlaceholder.insert(0, "(").insert(valuePlaceholder.length(), ")");
        builder.append(valuePlaceholder);
        return builder.toString();
    }

    public static String generateInsertSql(String schema, String tableName, Collection<String> fieldNames) {
        return generateInsertSql(schema, tableName, fieldNames);
    }

    public static String generateMultiInsertSql(String schema, String tableName, Collection<String> fieldNames, int rowNumber,String dbTypeName) {
        StringBuilder insertSqlWithoutValues = generateMultiInsertSqlWithoutValues(schema, tableName, fieldNames,dbTypeName);
        StringBuilder valuePlaceholder = new StringBuilder(Constans.EMPTY_STRING);
        for (int i = 0; i < fieldNames.size(); i++) {
            valuePlaceholder.append("?,");
        }
        if (valuePlaceholder.length() > 0)
            valuePlaceholder.deleteCharAt(valuePlaceholder.length() - 1);
        valuePlaceholder.insert(0, "(")
                .insert(valuePlaceholder.length(), ")");

        StringBuilder valuesJoinPlaceholder = new StringBuilder(Constans.EMPTY_STRING);
        for (int i = 0; i < rowNumber; i++) {
            valuesJoinPlaceholder.append(valuePlaceholder)
                    .append(",");
        }
        if (valuesJoinPlaceholder.length() > 0)
            valuesJoinPlaceholder.deleteCharAt(valuesJoinPlaceholder.length() - 1);
        return insertSqlWithoutValues.append(valuesJoinPlaceholder).toString();
    }

    public static LinkedHashMap<String, Field> resultSetToFields(
            ResultSet rs,
            int maxClobSize,
            int maxBlobSize,
            Map<String, DataType> columnsToTypes,
            UnknownTypeAction unknownTypeAction,
            Set<String> recordHeader,
            boolean timestampToString,
            DatabaseVendor vendor,
            Map<String,String> aliasMap
    ) throws SQLException {
        ResultSetMetaData md = rs.getMetaData();
        LinkedHashMap<String, Field> fields = new LinkedHashMap<>(md.getColumnCount());
        for (int i = 1; i <= md.getColumnCount(); i++) {
            try {
                if (recordHeader != null && recordHeader.contains(md.getColumnName(i))) {
                    DataType dataType = columnsToTypes.get(md.getColumnName(i));
                    Field field = resultToField(
                            rs,
                            i,
                            maxClobSize,
                            maxBlobSize,
                            dataType == null ? DataType.USE_COLUMN_TYPE : dataType,
                            unknownTypeAction,
                            timestampToString,
                            vendor
                    );
                    fields.put(JdbcCharUtil.getAliasName(aliasMap,md.getColumnLabel(i)), field);
                }
            } catch (Exception e) {
                log.debug("Can't read from JDBC: {}", e.getMessage(), e);
            }
        }
        return fields;
    }

    public static Field resultToField(
            ResultSet rs,
            int columnIndex,
            int maxClobSize,
            int maxBlobSize,
            DataType userSpecifiedType,
            UnknownTypeAction unknownTypeAction,
            boolean timestampToString,
            DatabaseVendor vendor
    ) throws Exception {
        Field field;
        ResultSetMetaData md = rs.getMetaData();
        if (userSpecifiedType != DataType.USE_COLUMN_TYPE) {
            // If user specifies the data type, overwrite the column type returned by database.
            field = Field.create(Field.Type.valueOf(userSpecifiedType.getLabel()), rs.getObject(columnIndex));
        } else {
            // Firstly resolve some vendor specific types - we are careful in case that someone will be clashing
            if (vendor == DatabaseVendor.ORACLE) {
                switch (md.getColumnType(columnIndex)) {
                    case TableContextUtil.TYPE_ORACLE_BINARY_FLOAT:
                        float floatValue = rs.getFloat(columnIndex);
                        return Field.create(Field.Type.FLOAT, rs.wasNull() ? null : floatValue);
                    case TableContextUtil.TYPE_ORACLE_BINARY_DOUBLE:
                        double doubleValue = rs.getDouble(columnIndex);
                        return Field.create(Field.Type.DOUBLE, rs.wasNull() ? null : doubleValue);
                    case TableContextUtil.TYPE_ORACLE_TIMESTAMP_WITH_TIME_ZONE:
                    case TableContextUtil.TYPE_ORACLE_TIMESTAMP_WITH_LOCAL_TIME_ZONE:
                        OffsetDateTime offsetDateTime = rs.getObject(columnIndex, OffsetDateTime.class);
                        if (offsetDateTime == null) {
                            return timestampToString ?
                                    Field.create(Field.Type.STRING, null) :
                                    Field.create(Field.Type.ZONED_DATETIME, null);
                        }
                        if (timestampToString) {
                            return Field.create(Field.Type.STRING, offsetDateTime.toZonedDateTime().toString());
                        }
                        // Zoned Datetime can handle high precision
                        return Field.create(Field.Type.ZONED_DATETIME, offsetDateTime.toZonedDateTime());
                    case Types.SQLXML:
                        SQLXML xml = rs.getSQLXML(columnIndex);
                        return Field.create(Field.Type.STRING, xml == null ? null : xml.getString());
                }
            } else if (vendor == DatabaseVendor.SQL_SERVER) {

                throw new IllegalArgumentException("当前不支持SQL_SERVER数据库");
            } else if (vendor.isOneOf(DatabaseVendor.MYSQL, DatabaseVendor.MARIADB)) {

                int columnType = md.getColumnType(columnIndex);
                String columnTypeName = md.getColumnTypeName(columnIndex);
                if (columnType == Types.SMALLINT && columnTypeName.endsWith("UNSIGNED")) {
                    int value = rs.getInt(columnIndex);
                    return Field.create(Field.Type.INTEGER, rs.wasNull() ? null : value);
                }
                if (columnType == Types.INTEGER && columnTypeName.endsWith("UNSIGNED")) {
                    long value = rs.getInt(columnIndex);
                    return Field.create(Field.Type.LONG, rs.wasNull() ? null : value);
                }
                if (columnType == Types.BIGINT && columnTypeName.endsWith("UNSIGNED")) {
                    BigDecimal value = rs.getBigDecimal(columnIndex);
                    return Field.create(Field.Type.DECIMAL, rs.wasNull() ? null : value);
                }
            }

            switch (md.getColumnType(columnIndex)) {
                case Types.BIGINT:
                    field = Field.create(Field.Type.LONG, rs.getObject(columnIndex));
                    break;
                case Types.BINARY:
                case Types.LONGVARBINARY:
                case Types.VARBINARY:
                    field = Field.create(Field.Type.BYTE_ARRAY, rs.getBytes(columnIndex));
                    break;
                case Types.BIT:
                case Types.BOOLEAN:
                    field = Field.create(Field.Type.BOOLEAN, rs.getObject(columnIndex));
                    break;
                case Types.CHAR:
                case Types.LONGNVARCHAR:
                case Types.LONGVARCHAR:
                case Types.NCHAR:
                case Types.NVARCHAR:
                case Types.VARCHAR:
                    field = Field.create(Field.Type.STRING, rs.getObject(columnIndex));
                    break;
                case Types.CLOB:
                case Types.NCLOB:
                    field = Field.create(Field.Type.STRING, getClobString(rs.getClob(columnIndex), maxClobSize));
                    break;
                case Types.BLOB:
                    field = Field.create(Field.Type.BYTE_ARRAY, getBlobBytes(rs.getBlob(columnIndex), maxBlobSize));
                    break;
                case Types.DATE:
                    field = Field.create(Field.Type.DATE, rs.getDate(columnIndex));
                    break;
                case Types.DECIMAL:
                case Types.NUMERIC:
                    field = Field.create(Field.Type.DECIMAL, rs.getBigDecimal(columnIndex));
                    field.setAttribute(HeaderAttributeConstants.ATTR_SCALE, String.valueOf(rs.getMetaData().getScale(columnIndex)));
                    field.setAttribute(HeaderAttributeConstants.ATTR_PRECISION, String.valueOf(rs.getMetaData().getPrecision(columnIndex)));
                    break;
                case Types.DOUBLE:
                    field = Field.create(Field.Type.DOUBLE, rs.getObject(columnIndex));
                    break;
                case Types.FLOAT:
                case Types.REAL:
                    field = Field.create(Field.Type.FLOAT, rs.getObject(columnIndex));
                    break;
                case Types.INTEGER:
                    field = Field.create(Field.Type.INTEGER, rs.getObject(columnIndex));
                    break;
                case Types.ROWID:
                    field = Field.create(Field.Type.STRING, rs.getRowId(columnIndex).toString());
                    break;
                case Types.SMALLINT:
                case Types.TINYINT:
                    field = Field.create(Field.Type.SHORT, rs.getObject(columnIndex));
                    break;
                case Types.TIME:
                    field = Field.create(Field.Type.TIME, rs.getObject(columnIndex));
                    break;
                case Types.TIMESTAMP:
                    field = Field.create(Field.Type.OBJECT,rs.getObject(columnIndex));
                    break;
                case Types.TIME_WITH_TIMEZONE:
                    OffsetTime offsetTime = rs.getObject(columnIndex, OffsetTime.class);
                    field = Field.create(Field.Type.TIME, Date.from(offsetTime.atDate(LocalDate.MIN).toInstant()));
                    break;
                case Types.TIMESTAMP_WITH_TIMEZONE:
                    OffsetDateTime offsetDateTime = rs.getObject(columnIndex, OffsetDateTime.class);
                    field = Field.create(Field.Type.ZONED_DATETIME, offsetDateTime.toZonedDateTime());
                    break;
                //case Types.REF_CURSOR: // JDK8 only
                case Types.SQLXML:
                case Types.STRUCT:
                case Types.ARRAY:
                case Types.DATALINK:
                case Types.DISTINCT:
                case Types.JAVA_OBJECT:
                case Types.NULL:
                case Types.OTHER:
                case Types.REF:
                default:
                    if (unknownTypeAction == null) {
                        return null;
                    }
                    switch (unknownTypeAction) {
                        case THROW_EXCEPTION:
                            throw new Exception("字段类型未找到匹配项：" + md.getColumnType(columnIndex));
                        case CONVERT_TO_NULL:
                            return null;
                        case CONVERT_TO_STRING:
                            Object value = rs.getObject(columnIndex);
                            if (value != null) {
                                field = Field.create(Field.Type.STRING, rs.getObject(columnIndex).toString());
                            } else {
                                field = Field.create(Field.Type.STRING, null);
                            }
                            break;
                        default:
                            throw new IllegalStateException("未知的UnknownTypeAction: " + unknownTypeAction);
                    }
            }
        }

        return field;
    }

    private static HikariConfig createDataSourceConfig(
            HikariPoolConfig hikariPoolConfig,
            boolean autoCommit,
            boolean readOnly
    ) {
        HikariConfig config = new HikariConfig();
        config.setAutoCommit(autoCommit);
        config.setReadOnly(readOnly);
        fillHikariConfig(hikariPoolConfig, config);

        return config;
    }

    private static HikariConfig createDataSourceConfig(HikariPoolConfig hikariPoolConfig) {
        HikariConfig config = new HikariConfig();
        config.setAutoCommit(hikariPoolConfig.isAutoCommit());
        config.setReadOnly(hikariPoolConfig.isReadOnly());
        fillHikariConfig(hikariPoolConfig, config);
        return config;
    }

    private static void fillHikariConfig(HikariPoolConfig hikariPoolConfig, HikariConfig config) {
        config.setJdbcUrl(hikariPoolConfig.getConnectionString());
        config.setUsername(hikariPoolConfig.getUsername());
        config.setPassword(hikariPoolConfig.getPassword());
        config.setMaximumPoolSize(hikariPoolConfig.maximumPoolSize);
        config.setMinimumIdle(hikariPoolConfig.minIdle);
        config.setConnectionTimeout(hikariPoolConfig.connectionTimeout * HikariPoolConfig.MILLISECONDS);
        config.setIdleTimeout(hikariPoolConfig.idleTimeout * HikariPoolConfig.MILLISECONDS);
        config.setMaxLifetime(hikariPoolConfig.maxLifetime * HikariPoolConfig.MILLISECONDS);

        if (StrUtil.isNotEmpty(hikariPoolConfig.driverClassName)) {
            config.setDriverClassName(hikariPoolConfig.driverClassName);
        }

        if (hikariPoolConfig.transactionIsolation != TransactionIsolationLevel.DEFAULT) {
            config.setTransactionIsolation(hikariPoolConfig.transactionIsolation.name());
        }

        if (StrUtil.isNotEmpty(hikariPoolConfig.connectionTestQuery)) {
            config.setConnectionTestQuery(hikariPoolConfig.connectionTestQuery);
        }

        if (StrUtil.isNotEmpty(hikariPoolConfig.initialQuery)) {
            config.setConnectionInitSql(hikariPoolConfig.initialQuery);
        }

        config.setDataSourceProperties(hikariPoolConfig.getDriverProperties());
    }

    private static String getCatalog(Connection connection, String schema) throws SQLException {
        if (StrUtil.isEmpty(schema)) {
            return connection.getCatalog();
        }

        String name = connection.getMetaData().getDatabaseProductName().toLowerCase();

        for (String d : RDBMS_WITHOUT_SCHEMAS) {
            if (name.contains(d)) {
                return schema;
            }
        }

        return connection.getCatalog();
    }

    private static void setNanosecondsinAttribute(int nanoseconds, Field field) {
        final long actualNanos = nanoseconds % NANOS_TO_MILLIS_ADJUSTMENT;
        if (actualNanos > 0) {
            field.setAttribute(FIELD_ATTRIBUTE_NANOSECONDS, String.valueOf(actualNanos));
        }
    }

    private static String getClobString(Clob data, int maxClobSize) throws IOException, SQLException {
        if (data == null) {
            return null;
        }

        StringBuilder sb = new StringBuilder();
        int bufLen = 1024;
        char[] cbuf = new char[bufLen];

        // Read up to max clob length
        long maxRemaining = maxClobSize;
        int count;
        try (Reader r = data.getCharacterStream()) {
            while ((count = r.read(cbuf)) > -1 && maxRemaining > 0) {
                // If c is more then the remaining chars we want to read, read only as many are available
                if (count > maxRemaining) {
                    count = (int) maxRemaining;
                }
                sb.append(cbuf, 0, count);
                // decrement available according to the number of chars we've read
                maxRemaining -= count;
            }
        }
        return sb.toString();
    }

    private static byte[] getBlobBytes(Blob data, int maxBlobSize) throws IOException, SQLException {
        if (data == null) {
            return null;
        }

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        int bufLen = 1024;
        byte[] buf = new byte[bufLen];

        // Read up to max blob length
        long maxRemaining = maxBlobSize;
        int count;
        try (InputStream is = data.getBinaryStream()) {
            while ((count = is.read(buf)) > -1 && maxRemaining > 0) {
                // If count is more then the remaining bytes we want to read, read only as many are available
                if (count > maxRemaining) {
                    count = (int) maxRemaining;
                }
                os.write(buf, 0, count);
                // decrement available according to the number of bytes we've read
                maxRemaining -= count;
            }
        }
        return os.toByteArray();
    }

    private static String getClassJarLocation(Class klass) {
        return klass.getProtectionDomain().getCodeSource().getLocation().toString();
    }

    /**
     * 将自动提交修改为手动提交方式
     * @param connection
     * @return
     */
    public static boolean manualCommit(Connection connection){
        //是否调整
        boolean istz=false;
        try {
            if (connection.getAutoCommit()) {
                connection.setAutoCommit(false);
                istz = true;
            }
        } catch (SQLException e) {
            log.error("修改提交失败!",e);
        }
        return istz;
    }

    /**
     * 重置auto-commit状态
     * @param connection 连接
     * @param istz 是否调整
     */
    public static void resetAutoCommit(Connection connection,boolean istz){
        try {
            if((!connection.getAutoCommit()) && istz){
                connection.setAutoCommit(true);
            }
        } catch (SQLException e) {
            log.error("还原auto-commit失败!",e);
        }
    }

    /**
     * 关闭DB数据库相关信息
     * @param resultSet 结果集
     * @param statement 预处理
     * @param connection 连接
     */
    public static void closeDb(ResultSet resultSet, Statement statement, Connection connection){
        try {
            if(resultSet!=null){
                resultSet.close();
            }
        } catch (SQLException e) {
            log.error("resultSet关闭失败",e);
        }
        try {
            if(statement!=null){
                statement.close();
            }
        } catch (SQLException e) {
            log.error("statement关闭失败",e);
        }
        try {
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            log.error("connection关闭失败",e);
        }
    }
}